package com.information.repository;

import com.information.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    Optional<User> findByUsername(String username);

    @Query("SELECT u FROM User u WHERE u.username = :username AND u.password = :password AND u.isDelete = false AND u.status = 'ACTIVE'")
    Optional<User> findByUsernameAndPassword(@Param("username") String username, @Param("password") String password);

    boolean existsByUsername(String username);

    // 查询未删除的用户
    @Query("SELECT u FROM User u WHERE u.isDelete = false")
    List<User> findAllActiveUsers();

    // 根据角色查询用户
    @Query("SELECT u FROM User u WHERE u.role = :role AND u.isDelete = false")
    List<User> findByRole(@Param("role") User.UserRole role);

    // 根据状态查询用户
    @Query("SELECT u FROM User u WHERE u.status = :status AND u.isDelete = false")
    List<User> findByStatus(@Param("status") User.UserStatus status);

    // 查询未删除的用户（按ID）
    @Query("SELECT u FROM User u WHERE u.id = :id AND u.isDelete = false")
    Optional<User> findByIdAndNotDeleted(@Param("id") Long id);

    // 查询未删除的用户（按用户名）
    @Query("SELECT u FROM User u WHERE u.username = :username AND u.isDelete = false")
    Optional<User> findByUsernameAndNotDeleted(@Param("username") String username);

    // 检查用户名是否存在（未删除的用户）
    @Query("SELECT CASE WHEN COUNT(u) > 0 THEN true ELSE false END FROM User u WHERE u.username = :username AND u.isDelete = false")
    boolean existsByUsernameAndNotDeleted(@Param("username") String username);

    // 登录验证（用户名、密码、未删除、状态激活）
    @Query("SELECT u FROM User u WHERE u.username = :username AND u.password = :password AND u.isDelete = false AND u.status = 'ACTIVE'")
    Optional<User> findByUsernameAndPasswordAndNotDeleted(@Param("username") String username, @Param("password") String password);
    // 查询已删除的用户
    @Query("SELECT u FROM User u WHERE u.isDelete = true ORDER BY u.updatedAt DESC")
    List<User> findAllDeletedUsers();
    // 统计相关查询方法
    long countByIsDeleteFalse();
    long countByIsDeleteFalseAndStatus(User.UserStatus status);
    long countByIsDeleteFalseAndCreatedAtAfter(LocalDateTime date);
    List<User> findByIsDeleteFalseAndCreatedAtAfter(LocalDateTime date);
}